package webservices;

import interfaces.IFavouriteUpdateQuery;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import webservices.helper.Helper;

public class FavouriteUpdateQueryImpl implements IFavouriteUpdateQuery {
	
	public String query(String userID){
		String returnMessage = "";
		Connection con = null;
		try {
			con = Helper.getDBConnection();
			returnMessage = "<response name='success' />";
			
			ResultSet rs = con.createStatement().executeQuery("select c.courseid as courseid, c.name as coursename, h.changetime as changetime, h.log as log"
								+ " from favourite f, course c, changehistory h where f.userid = " + userID 
								+ " and f.courseid = c.courseid and f.courseid = h.courseid and changetime > date_sub(now(), interval 30 day) order by courseid, changetime desc");

			while (rs.next()){
				returnMessage += "<response name='changelog'>";
				returnMessage += "<courseID>" + rs.getString("courseid") + "</courseID>" ;
				returnMessage += "<courseName>" + rs.getString("coursename") + "</courseName>" ;
				returnMessage += "<changeTime>" + rs.getString("changetime") + "</changeTime>" ;
				returnMessage += "<log>" + rs.getString("log") + "</log>" ;
				returnMessage += "</response>";
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try{
				con.close();
			}catch(SQLException e) {}
		}
		return returnMessage;
	}
	
}